﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.Objects;
using System.Data.Common;

namespace Q.Framework.Data
{
    public class StoredProcedure
    {
        public StoredProcedure(String name)
        {
            Name = name;
            try
            {
                DbCommand command = Data.ContextManager.Context.Connection.CreateCommand();
                command.CommandType = CommandType.StoredProcedure;

                Command = command;

            }
            catch (Exception)
            {
                throw;
            }
        }

        private String Name { get; set; }

        private DbCommand Command { get; set; }

        public void InsertParameter(String name, object value, DbType type)
        {
            DbParameter param = Command.CreateParameter();
            param.ParameterName = name;
            param.Value = value;
            param.DbType = type;
            Command.Parameters.Add(param);
        }

        public void RemoveParameter(String name)
        {
            if (!Command.Parameters.Contains(name))
                throw new Exception(String.Format("Parâmetro '{0}' não encontrado na lista de Parâmetros", name));
            
            Command.Parameters.Remove(Command.Parameters.Cast<DbParameter>().First(p => p.ParameterName == name));
        }

        public void UpdateParameter(String name, object newValue, DbType type)
        {
            if (!Command.Parameters.Contains(name))
                throw new Exception(String.Format("Parâmetro '{0}' não encontrado na lista de Parâmetros", name));

            RemoveParameter(name);
            InsertParameter(name, newValue, type);
        }

        public DataTable ExecuteReader()
        {
            try
            {
                PrepareCommandToExecution();

                DbDataReader dr = Command.ExecuteReader();

                AfterExecution();

                return Utility.DataReaderUtility.GetTable(dr);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public void ExecuteNonQuery()
        {
            try
            {
                PrepareCommandToExecution();

                Command.ExecuteNonQuery();

                AfterExecution();
            }
            catch (Exception)
            {
                throw;
            }
        }

        private void PrepareCommandToExecution()
        {
            Command.CommandText = String.Format("EXEC {0} {1}", Name, String.Join(" , ", Command.Parameters.Cast<DbParameter>().Select(p => "@" + p.ParameterName)));
            CloseConnectionAfterExecute = false;

            if (Command.Connection.State == ConnectionState.Closed)
            {
                Command.Connection.Open(); CloseConnectionAfterExecute = true;
            }
        }

        private void AfterExecution()
        {
            if (CloseConnectionAfterExecute)
                Command.Connection.Close();
        }

        public bool CloseConnectionAfterExecute { get; set; }
    }
}